package sanrenxing.teacher.dao.impl;




import sanrenxing.Class.domain.ClassName;
import sanrenxing.teacher.dao.TeacherDao;
import sanrenxing.teacher.domain.RoleName;
import sanrenxing.teacher.domain.Teacher;
import sanrenxing.teacher.domain.TeacherTo;
import sanrenxing.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 编写者：胡标
 * 功能：教师管理
 */

public class TeacherDaoImpl implements TeacherDao {
    /**
     * 将教师实体对象添加到数据库teacher表中
     *
     * @param teacher 教师对象
     * @return 成功添加的行数(1 : 添加成功 ; 0 : 添加失败)
     */
    @Override
    public int add(Teacher teacher) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="insert into teacher values(?,?,?,?,?,?)";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1,teacher.getTeacherId());
            pstmt.setString(2,teacher.getTeacherName());
            pstmt.setInt(3,teacher.getRoleId());
            pstmt.setString(4,teacher.getTeacherNum());
            pstmt.setString(5,teacher.getTeacherPwd());
            pstmt.setString(6,teacher.getTeacherPhone());

            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据教师编号主键从数据库teacher表中删除指定教师数据
     *
     * @param teacherId 教师编号主键
     * @return 删除影响的行数(1 : 删除成功 ; 0 : 删除失败)
     */
    @Override
    public int delete(int teacherId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="delete from teacher where teacherId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1,teacherId);
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 将教师对象数据修改到数据库teacher表中
     *
     * @param teacher 教师对象
     * @return 修改影响的行数(1 : 修改成功 ; 0 : 修改失败)
     */
    @Override
    public int update(Teacher teacher) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="update teacher set teacherName=?,roleId=?,teacherNum=?," +
                    "teacherPwd=?,teacherPhone=? " +
                    "where teacherId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setString(1,teacher.getTeacherName());
            pstmt.setInt(2,teacher.getRoleId());
            pstmt.setString(3,teacher.getTeacherNum());
            pstmt.setString(4,teacher.getTeacherPwd());
            pstmt.setString(5,teacher.getTeacherPhone());
            pstmt.setInt(6,teacher.getTeacherId());
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据教师编号主键从数据库teacher表中查找单个教师
     *
     * @param teacherId 教师编号主键
     * @return 教师对象(如果返回null值 ， 表示查找失败)
     */
    @Override
    public Teacher findById(int teacherId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的教师对象
        Teacher teacher=null;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select teacherId,teacherName,roleId,teacherNum," +
                    "teacherPwd,teacherPhone from teacher where teacherId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1,teacherId);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //判断结果集，并把结果集的数据封装到教师对象中
            if(rs.next()){
                //实例化教师对象
                teacher=new Teacher();
                //把结果集数据赋值给教师对象各属性
                teacher.setTeacherId(rs.getInt("teacherId"));
                teacher.setTeacherName(rs.getString("teacherName"));
                teacher.setRoleId(rs.getInt("roleId"));
                teacher.setTeacherNum(rs.getString("teacherNum"));
                teacher.setTeacherPwd(rs.getString("teacherPwd"));
                teacher.setTeacherPhone(rs.getString("teacherPhone"));
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回教师对象
        return teacher;
    }

    /**
     * 查询数据库teacher表中所有教师
     *
     * @return 教师对象集合
     */
    @Override
    public List<Teacher> findAll() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的教师对象集合
        List<Teacher> teacherList=new ArrayList<Teacher>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select teacherId,teacherName,roleId,teacherNum,teacherPwd," +
                    "teacherPwd from teacher";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到教师对象中
            while(rs.next()){
                //定义教师对象
                Teacher teacher=new Teacher();
                //把结果集数据赋值给教师对象各属性
                teacher.setTeacherId(rs.getInt("teacherId"));
                teacher.setTeacherName(rs.getString("teacherName"));
                teacher.setRoleId(rs.getInt("roleId"));
                teacher.setTeacherNum(rs.getString("teacherNum"));
                teacher.setTeacherPwd(rs.getString("teacherPwd"));
                teacher.setTeacherPhone(rs.getString("teacherPhone"));
                //把教师对象添加教师集合中
                teacherList.add(teacher);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回教师对象集合
        return teacherList;
    }

    /**
     * 从数据库teacher表中查询指定页的教师对象集合
     *
     * @param rows  每页的教师行数
     * @param pages 页数
     * @return 当前页的教师对象集合
     */
    @Override
    public List<Teacher> findByPage(int rows, int pages) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的员工对象集合
        List<Teacher> teacherList=new ArrayList<Teacher>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select teacherId,teacherName,roleId,teacherNum,teacherPwd," +
                    "teacherPhone from teacher limit ?,?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给分页语句占位符赋值
            //分页语句：select * from 表名 limits (pages-1)*rows,rows
            pstmt.setInt(1,(pages-1)*rows);
            pstmt.setInt(2,rows);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到员工对象中
            while(rs.next()){
                //定义员工对象
                Teacher teacher=new Teacher();
                //把结果集数据赋值给员工对象各属性
                teacher.setTeacherId(rs.getInt("teacherId"));
                teacher.setTeacherName(rs.getString("teacherName"));
                teacher.setRoleId(rs.getInt("roleId"));
                teacher.setTeacherNum(rs.getString("teacherNum"));
                teacher.setTeacherPwd(rs.getString("teacherPhone"));
                teacher.setTeacherPhone(rs.getString("teacherPhone"));
                //把员工对象添加员工集合中
                teacherList.add(teacher);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回员工对象集合
        return teacherList;
    }

    /**
     * 统计数据库teacher表中记录总数
     *
     * @return 教师表中记录总数
     */
    @Override
    public int count() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as teachercount from teacher";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("teachercount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    /**
     * 条件分页查询
     *
     * @param rows      每页行数
     * @param pages     页数
     * @param condition 条件
     * @return 教师对象集合
     */
    @Override
    public List<TeacherTo> findByPage(int rows, int pages, String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的员工对象集合
        List<TeacherTo> teacherTList=new ArrayList<TeacherTo>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="SELECT t.teacherId,t.teacherName,r.roleName,t.teacherNum,t.teacherPwd,t.teacherPhone FROM" +
                    " teacher t JOIN role r ON t.roleId=r.roleId where 1=1 "+condition+" limit ?,?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给分页语句占位符赋值
            //分页语句：select * from 表名 limits (pages-1)*rows,rows
            pstmt.setInt(1,(pages-1)*rows);
            pstmt.setInt(2,rows);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到员工对象中
            while(rs.next()){
                //定义员工对象
                TeacherTo teacherTo=new TeacherTo();
                //把结果集数据赋值给员工对象各属性
                teacherTo.setTeacherId(rs.getInt("teacherId"));
                teacherTo.setTeacherName(rs.getString("teacherName"));
                teacherTo.setRoleName(rs.getString("roleName"));
                teacherTo.setTeacherNum(rs.getString("teacherNum"));
                teacherTo.setTeacherPwd(rs.getString("teacherPwd"));
                teacherTo.setTeacherPhone(rs.getString("teacherPhone"));
                //把员工对象添加员工集合中
                teacherTList.add(teacherTo);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回员工对象集合
        return teacherTList;
    }

    /**
     * 条件统计
     *
     * @param condition 条件
     * @return 教师数
     */
    @Override
    public int count(String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as teachercount from teacher where 1=1 "+condition;
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("teachercount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    /**
     * 获取查询条件
     *
     * @param teacher 教师对象
     * @return 条件字符串
     */
    @Override
    public String getCondition(Teacher teacher) {
        //定义查询条件字符串，初值为带空格的字符串(注意后续条件中的空格)
        String condition=" ";
        //如果员工对象为空，则直接返 回空字符串
        if(teacher==null){
            return condition;
        }
        //如果员工编号不为0
        if(teacher.getTeacherId()!=0){
            condition += " and teacherId="+teacher.getTeacherId();
        }
        //如果员工姓名不为空(字符串匹配模糊查询)
        if(teacher.getTeacherName()!=null && teacher.getTeacherName().trim().length()!=0){
            condition += " and teacherName like '%"+teacher.getTeacherName()+"%' ";
        }
        //如果员工编号不为0
        if(teacher.getRoleId()!=0){
            condition += " and roleId="+teacher.getRoleId();
        }
        //如果存在管理者编号
        if(teacher.getTeacherNum()!=null && teacher.getTeacherNum().trim().length()!=0){
            condition += " and teacherNum like '%"+teacher.getTeacherNum()+"%' ";
        }
        //如果存在管理者编号
        if(teacher.getTeacherPwd()!=null && teacher.getTeacherPwd().trim().length()!=0){
            condition += " and teacherPwd like '%"+teacher.getTeacherPwd()+"%' ";
        }
        //如果部门编号不为空
        if(teacher.getTeacherPhone()!=null && teacher.getTeacherPhone().trim().length()!=0){
            condition += " and teacherPhone like '%"+teacher.getTeacherPhone()+"%' ";
        }
        //返回条件字符串
        return condition;
    }

    /**
     * 查询所有角色
     * @return
     */
    @Override
    public List<RoleName> findRoleName() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集
        ResultSet rs=null;
        //定义一个对象集合
        List<RoleName> roleNameList=new ArrayList<>();


        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句
            String sql="SELECT  roleName FROM role";
            //实例化预编译语句
            pstmt=conn.prepareStatement(sql);
            //返回结果集
            rs=pstmt.executeQuery();
            while (rs.next()){
                //定义一个教师姓名
                RoleName roleName=new RoleName();
                //如果统计行数时没有指定别名，则用数字0作为参数
                roleName.setRoleName (rs.getString("roleName"));
                //将教师姓名添加到集合
                roleNameList.add(roleName);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回对象集合
        return roleNameList;
    }

    /**
     * 通过职务名称查询职务编号
     *
     * @param roleName 教师姓名
     * @return
     */
    @Override
    public int findRoleId(String roleName) {
        //定义一个角色编号
        int roleId;
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集
        ResultSet rs=null;


        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句
            String sql="SELECT  roleId FROM role where roleName=? ";
            //实例化预编译语句
            pstmt=conn.prepareStatement(sql);
            //给占位符赋值
            pstmt.setString(1,roleName);
            //返回结果集
            rs=pstmt.executeQuery();
            if (rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                roleId=rs.getInt("roleId");
                //返回教师编号
                return roleId;
            }

        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        }finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        return 0;
    }
}
